package com.zz.impl;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.transaction.Transactional;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import com.zz.dao.TeachersDao;
import com.zz.entity.Sections;
import com.zz.entity.Students;
import com.zz.entity.Teachers;
import com.zz.entity.TeachersSearch;
import com.zz.service.TeachersService;
@Service
public class TeachersImpl implements TeachersService {
@Autowired
TeachersDao teachersDao;
	@Override
	public List<Teachers> findAll() {
		// TODO Auto-generated method stub
		return teachersDao.findAll();
	}
	//分页多条件
	@Override
	public Page<Teachers> find(TeachersSearch t, Integer page, Integer limit) {
		System.out.println(t);
		if(page==null) page=1;
		if(limit==null) limit=10;
		Specification<Teachers> spec=getSpec(t);
		Pageable p=PageRequest.of(page-1, limit);
		
		return teachersDao.findAll(spec,p);
	}
	private Specification<Teachers> getSpec(TeachersSearch t) {
		return new Specification<Teachers>() {
			@Override
			public Predicate toPredicate(Root<Teachers> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
			Predicate p=criteriaBuilder.conjunction();
			p.getExpressions();
			List<Expression<Boolean>>list =p.getExpressions();
			Sections se=new Sections();
			if(t.getId()!=null) {
				se.setId(t.getId());
			}
			if(!StringUtils.isEmpty(t.getCardno())) {
				list.add(criteriaBuilder.equal(root.get("cardno"), t.getCardno()));
			}if(!StringUtils.isEmpty(t.getId())) {
				list.add(criteriaBuilder.equal(root.get("sections"), se));
			}
				return p;
			}
		};
	}
	//添加修改
	@Override
	public Integer save(Teachers t, Sections s) {
		t.setSections(s);
		if(teachersDao.save(t)!=null) {
			return 1;
		}else {
		return 0;
		}
	}
	//删除
	@Override
	public Integer delete(Integer id) {
		try {
			teachersDao.deleteById(id);
			return 1;
		} catch (Exception e) {
			return 0;
		}
		
	}
	@Override
	public Integer count() {
		// TODO Auto-generated method stub
		return (int)teachersDao.count();
	}
	@Override
	@Transactional(rollbackOn=Exception.class)
	public void getExcelTeachers(MultipartFile file) throws Exception {
		 List<Teachers> list = new ArrayList<Teachers>();
	        //1.得到上传的表
	        Workbook workbook2 = WorkbookFactory.create(file.getInputStream());
	        //2、获取sheet1工作表
	        Sheet sheet2 = workbook2.getSheet("sheet1");
	        //获取表的总行数
	        int num = sheet2.getLastRowNum();
	        //总列数
	        int col = sheet2.getRow(0).getLastCellNum();
	        //System.out.println("这是老师导入的实现层");
	     	//遍历excel每一行
	        for (int j = 1; j < num; j++) {
	            Row row1 = sheet2.getRow(j);
	            //获取表中第i行，第2列的单元格
	            Cell cell3 = row1.getCell(0);
	            //excel表的第i行，第3列的单元格
	            Cell cell4 = row1.getCell(1);
	            Cell cell5 = row1.getCell(2);
	            Cell cell7 = row1.getCell(3);
	            cell3.setCellType(CellType.STRING); //如果单元格中有数字或者其他格式的数据，则调用setCellType()转换为string类型
	            cell4.setCellType(CellType.STRING);
	            cell5.setCellType(CellType.STRING);
	            cell7.setCellType(CellType.STRING);
	         	//这里new 一个对象，用来装填从页面上传的Excel数据，字段根据上传的excel决定
	            Teachers t= new Teachers();
	            t.setCardno(cell3.getStringCellValue());
			    t.setName(cell4.getStringCellValue());
				t.setSex(cell5.getStringCellValue());
				t.setStatus(cell7.getStringCellValue());
				list.add(t);
				 System.out.println(list+","+j+","+num);	
	        } 
	        teachersDao.saveAll(list);
		
	}
	
	
	

}
